package com.sdust.utils;

import com.sdust.model.Book;
import com.sdust.model.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

public class jdbcUtils {
    public static final String url =
            "jdbc:mysql://bj-cdb-ha5dhabo.sql.tencentcdb.com:63720/experiment?useUnicode=true&characterEncoding=utf8&useSSL=true";
    public static final String username="temporary_user";
    public static final String password="temporary_user";

    public static Connection connection = null;
    public static Statement statement = null;

    public static final void createStatement() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        //3.连接成功，数据库对象 connection代表数据库
        connection = DriverManager.getConnection(url, username, password);

        //4.执行SQL对象  Statement 执行SQL的对象
        statement = connection.createStatement();
    }

    public static final List<Book> getAllBooks() throws Exception {
        createStatement();

        String sql = "SELECT * FROM Book";
        //返回的结果集,结果集中封装了我们全部查询出来的结果
        ResultSet resultSet = statement.executeQuery(sql);
        List<Book> books = new ArrayList<>();
        while (resultSet.next()){

            Integer id = resultSet.getObject("id", Integer.class);
            String name = resultSet.getObject("name", String.class);
            String author = resultSet.getObject("author", String.class);
            Double price = resultSet.getObject("price", Double.class);
            String publisher = resultSet.getObject("publisher", String.class);
            LocalDateTime publish_date = resultSet.getObject("publish_date", LocalDateTime.class);

            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            Book book = new Book(id, name, author, price, publisher, publish_date);
            books.add(book);
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
        return books;
    }

    public static final User getUserByName(String username) throws Exception {
        createStatement();
        String sql = "select * from User where user_name=" + "'" + username + "'";
        ResultSet resultSet = statement.executeQuery(sql);
        User user = new User();
        while (resultSet.next()) {
            Integer id = resultSet.getObject("id", Integer.class);
            String user_name = resultSet.getObject("user_name", String.class);
            String pass_word = resultSet.getObject("pass_word", String.class);
            user.setId(id);
            user.setUser_name(user_name);
            user.setPass_word(pass_word);
        }
        // 释放连接
        resultSet.close();
        statement.close();
        connection.close();
        return user;
    }

    public static final Boolean addBook(Book book) throws Exception {
        createStatement();
        String name = book.getName();
        String author = book.getAuthor();
        Double price = book.getPrice();
        String publisher = book.getPublisher();
        LocalDateTime publish_date = book.getPublish_date();
        if (author==null || name==null || price==null || publisher==null || publish_date==null) {
            return false;
        }

        // 检查数据名称与作者是否存在
        String checkSql = "select * from Book where name='" + name + "' and author='" + author + "';";
        ResultSet resultSet = statement.executeQuery(checkSql);
        if (resultSet.next()) {
            return false;
        }

        // 指定日期时间格式
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        // 格式化LocalDateTime为字符串
        String formattedDateTime = publish_date.format(formatter);

        String sql = "insert into Book (name, author, price, publisher, publish_date) values ("
                + "'" + name + "', "
                + "'" + author + "', "
                + price + ", "
                + "'" + publisher + "', "
                + "'" + formattedDateTime + "');";
        statement.execute(sql);

        // 释放连接
        statement.close();
        connection.close();
        return true;
    }

    public static final Boolean deleteBookById(Integer id) throws Exception {
        createStatement();

        String sql = "delete from Book where id = " + id;
        statement.execute(sql);

        // 释放连接
        statement.close();
        connection.close();
        return false;
    }

    public static final Boolean addUser(User user) throws Exception {
        createStatement();
        String user_name = user.getUser_name();
        String pass_word = user.getPass_word();

        if(user_name == null || pass_word == null) {
            return false;
        }

        String sql = "insert into User (user_name, pass_word) values ("
                + "'" + user_name + "', "
                + "'" + pass_word + "');";
        statement.execute(sql);
        return true;
    }
}
